package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.Beans.Photos;

@Repository (value="photodao")
public class PhotoDAOImpl implements PhotoDAO{
	@Autowired
	private JdbcTemplate jt;

	@Override
	public int add(final Photos photo) {
		// TODO Auto-generated method stub
		int id=0;
		if(photo!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_ADD(?,?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photo.getAlbumID());
							stm.setInt(2, photo.getLenth());
							stm.setInt(3, photo.getWidth());
							stm.setString(4, photo.getName());
							stm.setString(5, photo.getDescription());
							stm.setBytes(6, photo.getPreview());
							stm.setBytes(7, photo.getImage());
							stm.registerOutParameter(8, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(8);
								obj[1]=rs;
								return obj;
							}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@Override
	public boolean delete(final int pid) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(pid>0)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_DELETE(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=rs;
								return obj;
							}});
			sucess=true;
		}
		return sucess;
	}

	@Override
	public boolean edit(final Photos photo, final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if((photo!=null)&&(id>0))
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_EDIT(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, photo.getName());
							stm.setString(2, photo.getDescription());
							stm.setInt(3, id);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=rs;
								return obj;
							}});
			sucess=true;
		}
		return sucess;
	}

	@Override
	public Photos get(final int pid) {
		// TODO Auto-generated method stub
		if(pid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_GET(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								Photos photo =new Photos();
								if (rs.next())
								{
									photo.setAlbumID(rs.getInt("albumid"));
									photo.setCommented(rs.getInt("commented"));
									photo.setDescription(rs.getString("description"));
									photo.setId(rs.getInt("id"));
									photo.setLastViewed(rs.getTimestamp("lastviewed"));
									photo.setLenth(rs.getInt("length"));
									photo.setName(rs.getString("title"));
									photo.setPhotopreview(rs.getBoolean("ispreview"));
									photo.setSuggested(rs.getBoolean("suggested"));
									photo.setUploaded(rs.getTimestamp("uploaded"));
									photo.setWidth(rs.getInt("width"));
									photo.setViewed(rs.getInt("viewed"));
									photo.setCommented(rs.getInt("competitionid"));
									obj[1]=photo;
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (Photos) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getAllSuggested() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL PHOTO_GETALLSUGGESTED();";
						CallableStatement stm=conn.prepareCall(sql);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=null;
							List<Integer> users=new ArrayList<Integer>();
							while (rs.next())
							{
								users.add(rs.getInt("id"));
							}
							obj[1]=users;
							return obj;
						}});
		return (List<Integer>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getByAlbum(final int aid) {
		// TODO Auto-generated method stub
		if (aid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_GETBYALBUM(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, aid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								List<Integer> competitions = new ArrayList<Integer>();
								while (rs.next())
								{
									competitions.add(rs.getInt("id"));
								}
								if (competitions.size()>0)
									obj[1]=competitions;
								return obj;
							}});
			if(obj[1]!=null)
				return (List<Integer>) obj[1];
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getByResult(final int rid) {
		// TODO Auto-generated method stub
		if (rid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_GETBYRESULT(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, rid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								List<Integer> competitions = new ArrayList<Integer>();
								while (rs.next())
								{
									competitions.add(rs.getInt("id"));
								}
								if (competitions.size()>0)
									obj[1]=competitions;
								return obj;
							}});
			if(obj[1]!=null)
				return (List<Integer>) obj[1];
		}
		return null;
	}

	@Override
	public boolean join(final int pid, final int rid) {
		// TODO Auto-generated method stub
		if((pid>0)&&(rid>0))
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_JOIN(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							stm.setInt(2, rid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
				return true;
		}
		return false;
	}

	@Override
	public boolean quit(final int pid, final int rid) {
		// TODO Auto-generated method stub
		if((pid>0)&&(rid>0))
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_QUIT(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							stm.setInt(2, rid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
				return true;
		}
		return false;
	}

	@Override
	public boolean move(final int pid, final int naid) {
		// TODO Auto-generated method stub
		if((pid>0)&&(naid>0))
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_MOVE(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							stm.setInt(2, naid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
				return true;
		}
		return false;
	}

	@Override
	public boolean suggest(final int pid) {
		// TODO Auto-generated method stub
		if(pid>0)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_SUGGEST(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
				return true;
		}
		return false;
	}

	@Override
	public boolean unsuggest(final int pid) {
		// TODO Auto-generated method stub
		if(pid>0)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_UNSUGGEST(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
				return true;
		}
		return false;
	}

	@Override
	public byte[] getImage(final int pid) {
		// TODO Auto-generated method stub
		if(pid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_GETIMAGE(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("image");
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (byte[]) obj[1];
		}
		return null;
	}

	@Override
	public byte[] getPreview(final int pid) {
		// TODO Auto-generated method stub
		if(pid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_GETPREVIEW(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("preview");
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (byte[]) obj[1];
		}
		return null;
	}

	@Override
	public boolean setPreview(final int pid) {
		// TODO Auto-generated method stub
		if(pid>0)
		{
			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL PHOTO_SETPREVIEW(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, pid);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=null;
							obj[1]=rs;
							return obj;
						}});
				return true;
		}
		return false;
	}

}
